package com.example.ems.dao;

import com.example.ems.bean.Salary;
import com.example.ems.util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SalaryDAO {
    public List<Salary> getAllSalaries() {
        List<Salary> salaries = new ArrayList<>();
        String sql = "SELECT s.*, e.name as emp_name FROM salaries s JOIN employees e ON s.emp_id = e.id ORDER BY s.salary_year DESC, s.salary_month DESC, e.name";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                Salary sal = new Salary();
                sal.setId(rs.getInt("id"));
                sal.setEmpId(rs.getInt("emp_id"));
                sal.setEmpName(rs.getString("emp_name"));
                sal.setSalaryYear(rs.getInt("salary_year"));
                sal.setSalaryMonth(rs.getInt("salary_month"));
                sal.setAmount(rs.getDouble("amount"));
                sal.setStatus(rs.getString("status"));
                salaries.add(sal);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return salaries;
    }

    public Salary getSalaryById(int id) {
        String sql = "SELECT s.*, e.name as emp_name FROM salaries s JOIN employees e ON s.emp_id = e.id WHERE s.id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Salary sal = new Salary();
                    sal.setId(rs.getInt("id"));
                    sal.setEmpId(rs.getInt("emp_id"));
                    sal.setEmpName(rs.getString("emp_name"));
                    sal.setSalaryYear(rs.getInt("salary_year"));
                    sal.setSalaryMonth(rs.getInt("salary_month"));
                    sal.setAmount(rs.getDouble("amount"));
                    sal.setStatus(rs.getString("status"));
                    return sal;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean addSalary(Salary salary) {
        String sql = "INSERT INTO salaries (emp_id, salary_year, salary_month, amount, status) VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, salary.getEmpId());
            pstmt.setInt(2, salary.getSalaryYear());
            pstmt.setInt(3, salary.getSalaryMonth());
            pstmt.setDouble(4, salary.getAmount());
            pstmt.setString(5, salary.getStatus());
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean updateSalary(Salary salary) {
        String sql = "UPDATE salaries SET emp_id = ?, salary_year = ?, salary_month = ?, amount = ?, status = ? WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, salary.getEmpId());
            pstmt.setInt(2, salary.getSalaryYear());
            pstmt.setInt(3, salary.getSalaryMonth());
            pstmt.setDouble(4, salary.getAmount());
            pstmt.setString(5, salary.getStatus());
            pstmt.setInt(6, salary.getId());
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean deleteSalary(int id) {
        String sql = "DELETE FROM salaries WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<Salary> getSalariesByEmployeeName(String searchEmpName) {
        List<Salary> salaries = new ArrayList<>();
        String sql = "SELECT s.*, e.name as emp_name FROM salaries s " +
                "JOIN employees e ON s.emp_id = e.id " +
                "WHERE e.name LIKE ? " + // 按员工姓名模糊查询
                "ORDER BY s.salary_year DESC, s.salary_month DESC, e.name";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "%" + searchEmpName + "%");
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Salary sal = new Salary();
                    sal.setId(rs.getInt("id"));
                    sal.setEmpId(rs.getInt("emp_id"));
                    sal.setEmpName(rs.getString("emp_name"));
                    sal.setSalaryYear(rs.getInt("salary_year"));
                    sal.setSalaryMonth(rs.getInt("salary_month"));
                    sal.setAmount(rs.getDouble("amount"));
                    sal.setStatus(rs.getString("status"));
                    salaries.add(sal);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return salaries;
    }
}
